package com.library.util;

// DBUtil.java - 完整的数据库工具类

import java.sql.*;
import java.util.Properties;

public class DBUtil {
    // 数据库配置
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/library_management_system?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
    private static final String USERNAME = "library_user";
    private static final String PASSWORD = "library_password";

    // 静态块加载驱动
    static {
        try {
            Class.forName(DRIVER);
            System.out.println("✅ 数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            System.err.println("❌ 数据库驱动加载失败: " + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     */
    public static Connection getConnection() {
        try {
            Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("✅ 数据库连接成功");
            return conn;
        } catch (SQLException e) {
            System.err.println("❌ 获取数据库连接失败: " + e.getMessage());
            System.err.println("请检查:");
            System.err.println("1. MySQL服务是否启动");
            System.err.println("2. 数据库是否存在: library_management_system");
            System.err.println("3. 用户名密码是否正确: " + USERNAME + "/" + PASSWORD);
            System.err.println("4. 连接URL: " + URL);
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭连接
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
                System.out.println("✅ 数据库连接已关闭");
            } catch (SQLException e) {
                System.err.println("❌ 关闭连接失败: " + e.getMessage());
            }
        }
    }

    /**
     * 关闭Statement
     */
    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                System.err.println("❌ 关闭Statement失败: " + e.getMessage());
            }
        }
    }

    /**
     * 关闭ResultSet
     */
    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                System.err.println("❌ 关闭ResultSet失败: " + e.getMessage());
            }
        }
    }

    /**
     * 关闭所有资源
     */
    public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
        closeResultSet(rs);
        closeStatement(stmt);
        closeConnection(conn);
    }

    /**
     * 测试数据库连接
     */
    public static boolean testConnection() {
        Connection conn = null;
        try {
            conn = getConnection();
            if (conn != null && !conn.isClosed()) {
                System.out.println("✅ 数据库连接测试成功");
                return true;
            }
        } catch (SQLException e) {
            System.err.println("❌ 数据库连接测试失败: " + e.getMessage());
        } finally {
            closeConnection(conn);
        }
        return false;
    }

    /**
     * 执行查询并返回结果集
     */
    public static ResultSet executeQuery(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            rs = pstmt.executeQuery();
            return rs;

        } catch (SQLException e) {
            System.err.println("❌ 执行查询失败: " + e.getMessage());
            System.err.println("SQL: " + sql);
            closeAll(conn, pstmt, null);
            return null;
        }
    }

    /**
     * 执行更新操作（INSERT, UPDATE, DELETE）
     */
    public static int executeUpdate(String sql, Object... params) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i + 1, params[i]);
            }

            int result = pstmt.executeUpdate();
            System.out.println("✅ 执行更新成功，影响行数: " + result);
            return result;

        } catch (SQLException e) {
            System.err.println("❌ 执行更新失败: " + e.getMessage());
            System.err.println("SQL: " + sql);
            return -1;
        } finally {
            closeAll(conn, pstmt, null);
        }
    }
}